﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace THenu.TinyHourseLib.DesignPattern.Behavioral_Patterns.Template_Method
{
    /// <summary>
    /// The 'AbstractClass' abstract class
    /// </summary>
    abstract class DataAccessObject
    {
        protected string connectionString;
        protected DataSet dataSet;

        public virtual void Connect()
        {
            string fileDB = "..\\..\\..\\db1.mdb";
            // Make sure mdb is available to app
            connectionString = "provider=Microsoft.JET.OLEDB.4.0;data source=" + fileDB;
            if (!File.Exists(fileDB))
            {

                ADOX.CatalogClass access = new ADOX.CatalogClass();
                access.Create(connectionString);

                //创建一个表
                ADOX.TableClass table = new ADOX.TableClass();
                table.ParentCatalog = access;
                table.Name = "Categories";
                //创建一个自动增长字段
                ADOX.ColumnClass col = new ADOX.ColumnClass();
                col.ParentCatalog = access;
                col.Type = ADOX.DataTypeEnum.adVarWChar;//设置字段类型
                col.Name = "CategoryName";
                //col.Properties["jet OLEDB:Allow Zero Length"].Value = false;
                //col.Properties["AutoIncrement"].Value = true;
                table.Columns.Append(col);
                access.Tables.Append(table);


                //创建一个表
                ADOX.TableClass tableProduct = new ADOX.TableClass();
                tableProduct.ParentCatalog = access;
                tableProduct.Name = "Products";
                //创建一个自动增长字段
                ADOX.ColumnClass colProduct = new ADOX.ColumnClass();
                colProduct.ParentCatalog = access;
                colProduct.Type = ADOX.DataTypeEnum.adVarWChar;//设置字段类型
                colProduct.Name = "ProductName";
                //col.Properties["jet OLEDB:Allow Zero Length"].Value = false;
                //col.Properties["AutoIncrement"].Value = true;
                tableProduct.Columns.Append(colProduct);
                access.Tables.Append(tableProduct);


                using (OleDbConnection strOleDbConnection = new OleDbConnection(connectionString))
                {
                    OleDbCommand insert = new OleDbCommand(null, strOleDbConnection);
                    strOleDbConnection.Open();
                    string[] cateName = { "Fruit", "Book" };
                    string[] Product = { "Apple", "What i love you" };
                    for (int i = 0; i < cateName.Length; i++)
                    {
                        insert.CommandText = string.Format("INSERT INTO  Categories (CategoryName) VALUES ( '{0}')", cateName[i]);

                        insert.ExecuteNonQuery();
                        insert.CommandText = string.Format("INSERT INTO  Products (ProductName) VALUES ( '{0}')", Product[i]);
                        insert.ExecuteNonQuery();
                    }

                    strOleDbConnection.Close();
                }
            }
        }

        public abstract void Select();
        public abstract void Process();

        public virtual void Disconnect()
        {
            connectionString = "";
        }

        // The 'Template Method' 
        public void Run()
        {
            Connect();
            Select();
            Process();
            Disconnect();
        }
    }

    /// <summary>
    /// A 'ConcreteClass' class
    /// </summary>
    class Categories : DataAccessObject
    {
        public override void Select()
        {
            string sql = "select CategoryName from Categories";
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(
              sql, connectionString);

            dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Categories");
        }

        public override void Process()
        {
            Console.WriteLine("Categories ---- ");

            DataTable dataTable = dataSet.Tables["Categories"];
            foreach (DataRow row in dataTable.Rows)
            {
                Console.WriteLine(row["CategoryName"]);
            }
            Console.WriteLine();
        }
    }

    /// <summary>
    /// A 'ConcreteClass' class
    /// </summary>
    class Products : DataAccessObject
    {
        public override void Select()
        {
            string sql = "select ProductName from Products";
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, connectionString);

            dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Products");
        }

        public override void Process()
        {
            Console.WriteLine("Products ---- ");
            DataTable dataTable = dataSet.Tables["Products"];
            foreach (DataRow row in dataTable.Rows)
            {
                Console.WriteLine(row["ProductName"]);
            }
            Console.WriteLine();
        }
    }
}
